Database

Database

• To connect with any database we require a Connector /Driver.
• Python provides us different connectors for different databases.
• Such as
  1. MySQL
  2. Microsoft SQL Server
  3. Oracle
  4. SqlLite
  5. Sybase

MySQL Driver/Connector

• Python needs a MySQL driver to communicate with MySQL server.
• We have to Download MySQL Driver/Connector.
• To Install My-SQL Connector use PIP tool.
  pip install mysql-connector
• This Connector /Driver is defined in package mysql.

PIP

• pip is the standard package manager for Python.
• It allows you to install and manage additional packages that are not part of the Python standard library.
• This tool is by default installed with python.
• This pip tool can be executed from command prompt.

Class MySQLConnection

• This class is used to manage session with database.
• It is defined in module mysql.connector.

• MySQLConnection(**kwargs)
  • Will connect with database and return a Connection Object if successful.
  • Arguments can be
  • user: The user name used to authenticate.
  • password: password to authenticate the user.
  • database: database name.
  • host: The host name or IP address (default is localhost ).
  • port: The TCP/IP port of the MySQL server(default is 3306).

• close() will close connection with database.
• rollback()commits the current transaction.
• close()Cancels the current transaction.
• Is connected() It returns True when the connection is available, False otherwise.
• cursor()This method returns a MySQLCursor() object.
  This Object can be used to send SQL statements to database.

On SQL prompt type commands:
sql> create database ccitdb;
sql> use ccitdb;
sql> create table accmaster(accno integer,name varchar(20),balance integer);
sql> insert into accmaster values(1001,'Amit Jain',50000);
sql> insert into accmaster values(1002,'Gopal Pandey',250000);
sql> insert into accmaster values(1003,'Mona Mantri',10000);

import mysql.connector as sql
try:
  conn=sql.MySQLConnection(user="root",password="admin",database="ccitdb")
  print("Connected...")
  conn.close()
except Exception as e:
  print("Failed to Connect",e)

values are 5 and 10
values are 5 and 10
values are 5 and 10